package com.Shoping.dao.impl;

import com.Shoping.dao.CommodityDao;
import com.Shoping.entity.Color;
import com.Shoping.entity.Commodity;
import com.Shoping.util.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class CommodityDaoImpl implements CommodityDao {
    private QueryRunner queryRunner = new QueryRunner();
    @Override
    public List<Commodity> selectByCgId(Integer cgId) throws SQLException {
        String sql = "SELECT cm_id cmId,cg_id cgId,cm_name cmName,cm_price cmPrice,cm_details cmDetails,cm_img cmImg FROM commodity WHERE cg_id=?;";
        return queryRunner.query(DbUtils.getConnection(),sql,new BeanListHandler<>(Commodity.class),cgId);
    }

    @Override
    public Commodity selectByCmId(Integer cmId) throws SQLException {
        String sql = "SELECT cm_id cmId,cg_id cgId,cm_name cmName,cm_price cmPrice,cm_details cmDetails,cm_img cmImg FROM commodity WHERE cm_id=?;";
        return queryRunner.query(DbUtils.getConnection(),sql,new BeanHandler<>(Commodity.class),cmId);
    }

    @Override
    public List<Color> selectColorByCmId(Integer cmId) throws SQLException {
        String sql = "SELECT c_id cId,cm_id cmId,c_img cImg,c_number cNumber,c_name cName FROM color WHERE cm_id=?;";
        return queryRunner.query(DbUtils.getConnection(),sql,new BeanListHandler<Color>(Color.class),cmId);
    }

    @Override
    public long count() throws SQLException {
        String sql = "SELECT COUNT(*) FROM commodity;";
        return queryRunner.query(DbUtils.getConnection(),sql,new ScalarHandler<>());
    }

    @Override
    public List<Commodity> selectByPath(int pageSize, int currentPath) throws SQLException {
        String sql = "SELECT cm_id cmId,cg_id cgId,cm_name cmName,cm_price cmPrice,cm_details cmDetails,cm_img cmImg FROM commodity LIMIT ?,?;";
        return queryRunner.query(DbUtils.getConnection(),sql,new BeanListHandler<>(Commodity.class),(currentPath-1)*pageSize,pageSize);
    }

    @Override
    public void modifyCommodityByCmId(Integer cmId) throws SQLException {
        String sql = "DELETE FROM commodity WHERE cm_id=?;";
        queryRunner.update(DbUtils.getConnection(),sql,cmId);
    }

    @Override
    public void updateCommodity(Commodity commodity) throws SQLException {
        String sql = "UPDATE commodity SET cg_id=?,cm_name=?,cm_price=?,cm_details=?,cm_img=? WHERE cm_id=?;";
        queryRunner.update(DbUtils.getConnection(),sql,commodity.getCgId(),commodity.getCmName(),commodity.getCmPrice(),commodity.getCmDetails(),commodity.getCmImg(),commodity.getCmId());
    }

    @Override
    public void addCommodity(Integer cgId, String cmName, Double cmPrice, String cmDetails, String cmImg) throws SQLException {
        String sql = "INSERT INTO commodity(cg_id,cm_name,cm_price,cm_details,cm_img) VALUES(?,?,?,?,?);";
        queryRunner.update(DbUtils.getConnection(),sql,cgId,cmName,cmPrice,cmDetails,cmImg);
    }

    @Override
    public Integer selectCmId() throws SQLException {
        String sql = "SELECT LAST_INSERT_ID();";
        return Integer.valueOf(queryRunner.query(DbUtils.getConnection(),sql,new ScalarHandler<>()).toString());
    }
}
